const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 * 商品上架2019-04-13 00:00:01.000
 */
// 上架的开始时间
let startTime = '2019-05-17 00:00:01.000';

/**
 * 
 * 获取pc上架商品的信息
 */
const getUpperShelfShop = async (ctx, next) => {
    const { pageNum, searchVal: { rkrq, gys, ypbm, isUpperShelf, time } } = ctx.request.body;
    let searchActionList = [];
    let publicSql = `FROM (SELECT RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,RM.SL,RM.SCPH,RM.YXQZ,RM.HWBH,RM.ID,RM.YPTM,
    RZ.DWBM,RZ.RQ FROM RKD_MX RM JOIN RKD_ZB RZ ON RM.ID = RZ.ID UNION ALL SELECT XM.YPBM,XM.YPMC,XM.YPGG,XM.JLDW,XM.JXMC,XM.SCCJ,XM.PZWH,XM.SL,XM.SCPH,XM.YXQZ,
    XM.HWBH,XM.ID,XM.YPTM,XZ.DWBM,XZ.RQ FROM XTD_MX XM JOIN XTD_ZB XZ ON XM.ID = XZ.ID) M  LEFT JOIN BM_YP B ON M.YPBM = B.BM LEFT JOIN YPSJ Y ON M.ID = Y.ID AND 
    M.YPBM = Y.YPBM AND M.SCPH = Y.SCPH LEFT JOIN BM_WLDW DW ON M.DWBM = DW.BM WHERE M.RQ>'${startTime}' `;

    let startSql = `SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY M.YPBM) AS px,Y.ID AS UP, M.ID,DW.MC AS DWMC,M.YPBM,M.YPTM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,
    M.SL AS DJSL,(ISNULL(M.SL,0)-ISNULL(Y.SJSL,0)) AS SL,M.SCPH,M.YXQZ,M.HWBH,B.CCTJ,B.YPLX,M.RQ ${publicSql} `;

    let totalSql = `SELECT COUNT(M.YPBM) AS total  ${publicSql} `;
    if (ypbm) {
        searchActionList.push(` AND (M.YPBM LIKE '%${ypbm}%' OR M.YPMC LIKE '%${ypbm}%' OR M.YPTM LIKE '%${ypbm}%' OR B.JP LIKE '%${ypbm}%')`);
    }
    if (gys) {
        searchActionList.push(` AND (DW.BM LIKE '%${gys}%' OR DW.MC LIKE '%${gys}%' OR DW.JP LIKE '%${gys}%' )`);
    }
    if (0 in rkrq) {
        searchActionList.push(` AND M.RQ >= '${rkrq[0]} 00:00:00' AND M.RQ <= '${rkrq[1]} 23:59:59' `);
    }
    if (isUpperShelf == 1) {
        if (0 in time) {
            searchActionList.push(` AND (Y.ID IS NOT NULL OR M.SL <= Y.SJSL) AND Y.SJRQ>= '${time[0]} 00:00:00' AND Y.SJRQ<= '${time[1]} 23:59:59' `);
        }
    } else {
        searchActionList.push(` AND (Y.ID IS NULL OR M.SL > Y.SJSL) `);
    }
    totalSql += ` ${searchActionList.join(' ')} `;
    startSql += ` ${searchActionList.join(' ')}) AS t1 WHERE t1.px BETWEEN ${(pageNum - 1) * 10 + 1} AND ${pageNum * 10}`;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { result: result.recordset, total: total.recordset[0].total }
}
// 补全时间
const repairTime = (time) => {
    return time.toString().length == 1 ? ('0' + time) : time;
}
// 获取时间拼接的年月日
const getNowTimeId = (time) => {
    let nowTime = time ? new Date(time) : new Date();
    let y = nowTime.getFullYear();
    let M = nowTime.getMonth() + 1;
    let d = nowTime.getDate();
    let h = nowTime.getHours();
    let m = nowTime.getMinutes();
    let s = nowTime.getSeconds();
    return {
        msDate: repairTime(m) + repairTime(s),
        date: y.toString().slice(2) + repairTime(M) + repairTime(d),
        fullDate: `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(h)}:${repairTime(m)}:${repairTime(s)}.000`
    }
}
/**
 * 
 * 上架商品
 */
const upperShelfShop = async (ctx, next) => {

    const { shopInfo, username } = ctx.request.body;
    let actionSqlList = [];
    // 如果是入库单时，在上架时改变库存和入库单的状态
    let endTime = new Date('2020-01-04').getTime();
    let rkTime = new Date(shopInfo.RQ).getTime()
    // 在2020-01-04以后的入库单上架的时候才会写入库存
    // if (shopInfo.DJLX === 'RKD' && rkTime >= endTime) {
    let sjsl = shopInfo.SJSL ? Number(shopInfo.SJSL) : 0;
    let dj = shopInfo.DJ ? Number(shopInfo.DJ) : 0;
    let je = Number((sjsl * dj).toFixed(2));


    // 更新库存动态表
    let kcslResult = await sql.query(`SELECT KCSL,KYSL,CBJJ,KCJE,SCRQ,YXQX,YXQZ FROM KC_KCDTB WHERE BMBM ='${shopInfo.BMBM}' AND YPBM = '${shopInfo.YPBM}' AND SCPH = '${shopInfo.SCPH}' AND CKBM = '${shopInfo.CKBM}'`);
    let kcInfo = kcslResult.recordset[0];
    // 如果有库存记录
    if (kcslResult.rowsAffected[0] > 0) {
        let kcsl = kcInfo.KCSL ? Number(kcInfo.KCSL) : 0;
        let kysl = kcInfo.KYSL ? Number(kcInfo.KYSL) : 0;
        let kcje = kcInfo.KCJE ? Number(kcInfo.KCJE) : 0;
        let cbjj = (kcsl !== 0 || sjsl !== 0) ? ((je + kcje) / (kcsl + sjsl)).toFixed(2) : 0;
        let newKcje = (cbjj * (kcsl + sjsl)).toFixed(2);
        let updateKcSql = `UPDATE KC_KCDTB SET RKRQ = '${baseFn.getNowTimeId().fullDate}',SCRQ = '${shopInfo.SCRQ}',YXQX = ${shopInfo.YXQX},YXQZ = '${shopInfo.YXQZ}',
            KCSL = ${kcsl + sjsl},KYSL = ${kysl + sjsl},HWBH = '${shopInfo.HWBH}',DWBM = '${shopInfo.DWBM}',DWMC = '${shopInfo.DWMC}',CBJJ=${cbjj},KCJE=${newKcje} 
            WHERE BMBM ='${shopInfo.BMBM}' AND YPBM = '${shopInfo.YPBM}' AND SCPH = '${shopInfo.SCPH}' AND CKBM = '${shopInfo.CKBM}' `;

        actionSqlList.push(updateKcSql);
    } else {
        // 如果没有库存记录
        let insertKcSql = `INSERT INTO KC_KCDTB(BMBM,CKBM,CKMC,YPBM,SCPH,RKRQ,SCRQ,YXQX,YXQZ,KCSL,
            KYSL,CBJJ,KCJE,DWBM,DWMC,HWBH,YHRQ) VALUES ('${shopInfo.BMBM}','${shopInfo.CKBM}',
            '${shopInfo.CKMC}','${shopInfo.YPBM}','${shopInfo.SCPH}','${baseFn.getNowTimeId().fullDate}',
            '${shopInfo.SCRQ}',${shopInfo.YXQX},'${shopInfo.YXQZ}',${sjsl},${sjsl},${dj},${je},
            '${shopInfo.DWBM}','${shopInfo.DWMC}','${shopInfo.HWBH}','')`;
        actionSqlList.push(insertKcSql);
    }
    let djlx = shopInfo.DJLX;
    console.log(djlx);
    // 判断入库单有没有全部上架完成
    let isFinishSql = ` select (RZ.ZSL - ISNULL(SJ.SJSL,0)) AS WSJSL,RZ.ZJE from (SELECT ID,ZSL,ZJE FROM ${djlx}_ZB  WHERE ID = '${shopInfo.ID}') RZ LEFT JOIN (SELECT SUM(SJSL) AS SJSL,
        ID FROM YPSJ WHERE ID = '${shopInfo.ID}' GROUP BY ID) SJ ON RZ.ID = SJ.ID `;
    let ysjslResult = await sql.query(isFinishSql);
    let ysjslInfo = ysjslResult.recordset[0];
    let wsjsl = ysjslInfo.WSJSL ? Number(ysjslInfo.WSJSL) : 0;
    let zje = ysjslInfo.ZJE ? Number(ysjslInfo.ZJE) : 0;
    // 如果上架完成，把入库单的审核状态改成已审核，并且改变往来单位的金额
    if (wsjsl <= sjsl) {
        if (djlx === 'RKD') {
            // 改变入库单的状态
            actionSqlList.push(` update RKD_ZB SET SH = 'T' WHERE ID = '${shopInfo.ID}' `);

            let dwInfoResult = await sql.query(`SELECT YINFYE,WLYE FROM BM_WLDW WHERE BM = '${shopInfo.DWBM}'`);
            let dwInfo = dwInfoResult.recordset[0];
            let yinfye = Number(dwInfo.YINFYE ? dwInfo.YINFYE : 0) + zje;
            let wlyf = Number(dwInfo.WLYE ? dwInfo.WLYE : 0) - zje;
            actionSqlList.push(`UPDATE BM_WLDW SET YINFYE = ${yinfye},WLYE = ${wlyf} WHERE BM = '${shopInfo.DWBM}'`);
        } else {
            actionSqlList.push(` update XTD_ZB SET SH = 'T' WHERE ID = '${shopInfo.ID}' `);
        }

    }

    // }

    // 更新条码
    if (shopInfo.hasOwnProperty('XTM')) {
        if ((!shopInfo.YPTM) && shopInfo.XTM) {
            actionSqlList.push(`UPDATE BM_YP SET TM ='${shopInfo.XTM}' WHERE BM = '${shopInfo.YPBM}'`);
        }
    }

    // 上架
    let isExistSql = `SELECT COUNT(ID) AS PX ,SJSL  FROM YPSJ WHERE ID = '${shopInfo.ID}' AND YPBM = '${shopInfo.YPBM}' AND SCPH = '${shopInfo.SCPH}' GROUP BY SJSL`;
    let isExist = await sql.query(isExistSql);
    let actionSql = '';
    if (isExist.recordset[0] && isExist.recordset[0].PX > 0) {
        actionSql = `UPDATE YPSJ SET SJSL =${Number(shopInfo.SJSL) + Number(isExist.recordset[0].SJSL)} WHERE  ID = '${shopInfo.ID}' AND YPBM = '${shopInfo.YPBM}' AND SCPH = '${shopInfo.SCPH}' `;

    } else {
        actionSql = `INSERT INTO YPSJ VALUES ('${shopInfo.ID}','${shopInfo.YPBM}','${shopInfo.SCPH}','${getNowTimeId().fullDate}','${username}',${shopInfo.SJSL}) `;

    }

    actionSqlList.push(actionSql);
    let lastSql = ` SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')} Commit Tran`;
    let result = await sql.query(lastSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }
}

/**
 * @params {day}距离今天是多少天
 * @description 获取距离今天多少天的时间
 * @value 时间格式
 */
// 获得三天前的时间 
const getDayAgo = (day) => {
    let nowStamp = (new Date()).getTime();
    let threeAgoStamp = nowStamp - day * 24 * 60 * 60 * 1000;
    let threeAgoTime = new Date(threeAgoStamp);
    let y = threeAgoTime.getFullYear();
    let m = threeAgoTime.getMonth() + 1;
    let d = threeAgoTime.getDate();
    return y + '-' + (m < 10 ? ('0' + m) : m) + '-' + (d < 10 ? ('0' + d) : d);
}
/**
 * 
 * 获取移动端上架商品的信息
 */
let xtd_time = '2020-03-25 23:59:59';
const getPhoneUpperShelfShop = async (ctx, next) => {
    const body = ctx.request.body;
    let searchVal = body.searchVal;
    let startSql = `SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER BY M.YPBM) AS px,M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,M.RQ,
    (ISNULL(M.SL,0)-ISNULL(Y.SJSL,0)) AS SL,M.SCPH,M.YXQZ,M.HWBH,B.CCTJ,B.YPLX,M.ID,M.DWMC,M.YPTM,M.BMBM,M.CKBM,M.CKMC,M.SCRQ,M.YXQX,M.DWBM,M.DJLX,M.DJ FROM 
    (SELECT RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,SUM(RM.SL) AS SL,RM.SCPH,RM.YXQZ,RM.HWBH,RM.ID,RM.YPTM,RZ.DWMC,RZ.RQ,RZ.BMBM,RM.CKBM,RM.CKMC,RM.SCRQ,RM.YXQX,RZ.DWBM,RM.DJ,'RKD' AS DJLX 
    FROM RKD_MX RM JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RZ.SH = 'F' GROUP BY RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,RM.SCPH,RM.YXQZ,
    RM.HWBH,RM.ID,RM.YPTM,RZ.DWMC,RZ.RQ,RZ.BMBM,RM.CKBM,RM.CKMC,RM.SCRQ,RM.YXQX,RZ.DWBM,RM.DJ UNION ALL SELECT XM.YPBM,XM.YPMC,XM.YPGG,XM.JLDW,XM.JXMC,XM.SCCJ,XM.PZWH,SUM(XM.SL),XM.SCPH,
    XM.YXQZ,XM.HWBH,XM.ID,XM.YPTM,XZ.DWMC,XZ.RQ,XZ.BMBM,XM.CKBM,XM.CKMC,XM.SCRQ,XM.YXQX,XZ.DWBM,XM.DJ,'XTD' AS DJLX FROM XTD_MX XM JOIN XTD_ZB XZ ON XM.ID = XZ.ID WHERE XZ.RQ > '${xtd_time}' AND  XZ.SH = 'F'  GROUP BY XM.YPBM,XM.YPMC,XM.YPGG,XM.JLDW,
    XM.JXMC,XM.SCCJ,XM.PZWH,XM.SCPH,XM.YXQZ,XM.HWBH,XM.ID,XM.YPTM,XZ.DWMC,XZ.RQ,XZ.BMBM,XM.CKBM,XM.CKMC,XM.SCRQ,XM.YXQX,XZ.DWBM,XM.DJ ) M LEFT JOIN BM_YP B ON M.YPBM = B.BM LEFT JOIN YPSJ Y 
    ON M.ID = Y.ID AND M.YPBM = Y.YPBM AND M.SCPH = Y.SCPH WHERE M.RQ>'${startTime}' AND M.RQ>'${getDayAgo(60)} 00:00:00.000'`;
    if (searchVal) {
        startSql += ` AND (M.YPBM LIKE '%${searchVal}%' OR B.MC LIKE '%${searchVal}%' OR B.TM LIKE '%${searchVal}%' OR B.JP LIKE '%${searchVal}%')`;
    }
    startSql += ` AND (Y.ID IS NULL OR M.SL > Y.SJSL)) AS t1  WHERE t1.px BETWEEN ${(body.page - 1) * 10 + 1} AND ${body.page * 10} `;
    console.log(startSql);
    let result = await sql.query(startSql);
    ctx.body = { result: result.recordset }
}







module.exports = {
    'POST /upperShelfShop': upperShelfShop,
    'POST /getUpperShelfShop': getUpperShelfShop,
    'POST /getPhoneUpperShelfShop': getPhoneUpperShelfShop


}